package com.byronspace.controller;

import io.swagger.annotations.ApiOperation;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.byronspace.model.Country;
import com.byronspace.model.Medal;
import com.byronspace.util.DBconn;
import com.byronspace.util.ReadCSV;
import com.byronspace.util.ReadFromFile;

@RestController
public class CountryController {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	private static final String CSVFilePath = "C:\\Users\\Administrator\\Desktop\\dictionary.csv";
	private static final String summerMedalCSVFilePath = "C:\\Users\\Administrator\\Desktop\\summer.csv";
	private static final String winterMedalCSVFilePath = "C:\\Users\\Administrator\\Desktop\\winter.csv";
	private static final String saveCountryFilePath = "C:\\Users\\Administrator\\Desktop\\country.json";
	private static final String saveMedalFilePath = "C:\\Users\\Administrator\\Desktop\\medal.json";
		
	@ApiOperation(value = "获取国家列表", notes = "")
	@RequestMapping(value = "/countries")
	public List<Map<String, Object>> getWeatherList() {
		String sql = "select * from country";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		for (Map<String, Object> map : list) {
			Set<Entry<String, Object>> entries = map.entrySet();
			if (entries != null) {
				Iterator<Entry<String, Object>> iterator = entries.iterator();
				while (iterator.hasNext()) {
					Entry<String, Object> entry = (Entry<String, Object>) iterator
							.next();
					Object key = entry.getKey();
					Object value = entry.getValue();
					System.out.println(key + ":" + value);
				}
			}
		}
		
		return list;
	}
	@ApiOperation(value = "将国家列表存入json文件", notes = "")
	@RequestMapping(value = "/saveCountryIntoFile")
	public String saveCountryIntoFile(){
		String sql = "select * from country";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);		
		for(int i=0;i<list.size();i++){
			String head = "{\"index\":{\"_index\":\"olympic\",\"_type\":\"country\",\"_id\":"+i+"}}";
			ReadFromFile.writeLog(head,saveCountryFilePath);
			ReadFromFile.writeLog(list.get(i).toString(),saveCountryFilePath);
		}
		return "success";
	}

	@ApiOperation(value = "从文本中获取国家列表编码与参数", notes = "")
	@RequestMapping(value = "/readCountriesFromFile")
	public List<Map<String, Object>>  getWeatherListFromWeb() throws Exception {
		
		ReadCSV readCSV = new ReadCSV();
		List<Country> countryList = readCSV.readCountriesFromCSV(CSVFilePath);	
		//清空国家表数据
		String deleteTableSQL = "delete from country ";
		jdbcTemplate.execute(deleteTableSQL);
		DBconn db = new DBconn();
		db.readList(countryList);
//		//插入国家数据   	
//		for(int i=0;i<countryList.size();i++){
//			String insertSQL= "INSERT INTO country(code, country,Population,GDP_per_Capita,create_date,update_date) VALUES ";
//			insertSQL += "('"+countryList.get(i).getCode()+"', '"+countryList.get(i).getCountry().replaceAll("'", "''")
//					+"','"+countryList.get(i).getPopulation()+"','"+countryList.get(i).getGDP_per_Capita() 	+"',?,?)";
//			System.out.println(insertSQL);
//			jdbcTemplate.update(insertSQL,new Object[] { new Date(),new Date()});
//		}
//		
		
		//返回插入后的数据
		String sql = "select * from country";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}
	@ApiOperation(value = "从文本中获取奥运奖牌列表编码与参数", notes = "")
	@RequestMapping(value = "/readMedalFromFile")
	public List<Map<String, Object>>  getSummerMedalListFromWeb() throws Exception {
		
		ReadCSV readCSV = new ReadCSV();
		List<Medal> medalList = readCSV.readMedalsFromCSV(summerMedalCSVFilePath,1);
		List<Medal> winterMedalList = readCSV.readMedalsFromCSV(winterMedalCSVFilePath, 2);
		for(int i=0;i<winterMedalList.size();i++){
			medalList.add(winterMedalList.get(i));
		}
		//清空奖牌表数据
		String deleteTableSQL = "delete from olympicMedal ";
		jdbcTemplate.execute(deleteTableSQL);
		//批量插入
		DBconn db = new DBconn();
		db.readMedalList(medalList);
//		for(int i=0;i<medalList.size();i++){
//			//插入数据
//	    	String insertSQL= "INSERT INTO olympicMedal(year, city,sport,discipline,athlete,country,gender,event,medal,type,create_date,update_date) VALUES ";   	
//			insertSQL += "('"+medalList.get(i).getYear()+"','"+medalList.get(i).getCity().replaceAll("'", "''")+"','"+medalList.get(i).getSport()
//					+"','"+medalList.get(i).getDiscipline()+"', '"+medalList.get(i).getAthlete().replaceAll("'", "''")
//					+"','"+medalList.get(i).getCountry().replaceAll("'", "''")
//					+"', '"+medalList.get(i).getGender()+"', '"+medalList.get(i).getEvent()
//					+"', '"+medalList.get(i).getMedal()+"', '"+medalList.get(i).getType()+"',?,?)";
//			System.out.println(insertSQL);
//			jdbcTemplate.update(insertSQL,new Object[] { new Date(),new Date()});
//		}
		
		
		//返回插入后的数据
		String sql = "select * from olympicMedal";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}
	@ApiOperation(value = "将奖牌列表存入json文件", notes = "")
	@RequestMapping(value = "/saveMedalIntoFile")
	public String saveMedalIntoFile(){
		String sql = "select * from olympicmedal";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);		
		for(int i=0;i<list.size();i++){
			String head = "{\"index\":{\"_index\":\"olympic\",\"_type\":\"medal\",\"_id\":"+i+"}}";
			ReadFromFile.writeLog(head,saveMedalFilePath);
			ReadFromFile.writeLog(list.get(i).toString(),saveMedalFilePath);
		}
		return "save medal to json file success";
	}
}
